"""
Dashboard 统计页面控制器
"""
from fastapi import APIRouter, Depends, Request
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, func, and_, or_
from datetime import datetime, timedelta

from config.get_db import get_db
from utils.response_util import ResponseUtil
from utils.log_util import logger
from module_admin.entity.do.client_do import AgentClient
from tcp_server.tcp_clients import ClientManager

dashboardRouter = APIRouter(prefix="/dashboard", tags=["系统首页"])


@dashboardRouter.get("/statistics")
async def get_statistics(
    request: Request,
    db: AsyncSession = Depends(get_db)
):
    """
    获取系统统计数据
    """
    try:
        # 1. 客户端总数（仅统计最近24小时内活跃的，避免统计历史垃圾数据）
        active_threshold = datetime.now() - timedelta(hours=24)
        total_clients_query = select(func.count(AgentClient.id)).where(
            or_(
                AgentClient.last_heartbeat >= active_threshold,
                AgentClient.online_status == 'online'
            )
        )
        total_clients_result = await db.execute(total_clients_query)
        total_clients = total_clients_result.scalar() or 0
        
        # 2. 在线客户端数量（从内存获取）
        online_clients = len(ClientManager.clients)
        
        # 3. 已登录客户端数量
        logged_in_query = select(func.count(AgentClient.id)).where(
            AgentClient.login_status == 'logged_in'
        )
        logged_in_result = await db.execute(logged_in_query)
        logged_in_clients = logged_in_result.scalar() or 0
        
        # 4. 浏览器已启动的客户端数量
        browser_running_query = select(func.count(AgentClient.id)).where(
            AgentClient.browser_status == 'running'
        )
        browser_running_result = await db.execute(browser_running_query)
        browser_running = browser_running_result.scalar() or 0
        
        # 5. 今日下单数量（如果有order_record表）
        today_orders = 0
        total_orders = 0
        try:
            from module_admin.entity.do.order_record_do import OrderRecord
            
            today_start = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
            today_orders_query = select(func.count(OrderRecord.id)).where(
                OrderRecord.create_time >= today_start
            )
            today_orders_result = await db.execute(today_orders_query)
            today_orders = today_orders_result.scalar() or 0
            
            # 总下单数
            total_orders_query = select(func.count(OrderRecord.id))
            total_orders_result = await db.execute(total_orders_query)
            total_orders = total_orders_result.scalar() or 0
        except Exception as e:
            logger.debug(f"订单统计失败（可能表不存在）: {e}")
        
        # 6. 今日搜索次数（如果有search_record表）
        today_searches = 0
        total_searches = 0
        try:
            from module_admin.entity.do.search_record_do import SearchRecord
            
            today_start = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
            # ✅ 修复：SearchRecord 使用 first_found_time 而不是 search_time
            today_searches_query = select(func.count(SearchRecord.id)).where(
                SearchRecord.first_found_time >= today_start
            )
            today_searches_result = await db.execute(today_searches_query)
            today_searches = today_searches_result.scalar() or 0
            
            # 总搜索次数
            total_searches_query = select(func.count(SearchRecord.id))
            total_searches_result = await db.execute(total_searches_query)
            total_searches = total_searches_result.scalar() or 0
        except Exception as e:
            logger.debug(f"搜索统计失败（可能表不存在）: {e}")
        
        # 返回统计数据
        statistics = {
            'total_clients': total_clients,
            'online_clients': online_clients,
            'logged_in_clients': logged_in_clients,
            'browser_running': browser_running,
            'today_orders': today_orders,
            'total_orders': total_orders,
            'today_searches': today_searches,
            'total_searches': total_searches,
            'online_rate': round((online_clients / total_clients * 100) if total_clients > 0 else 0, 1),
            'login_rate': round((logged_in_clients / total_clients * 100) if total_clients > 0 else 0, 1)
        }
        
        return ResponseUtil.success(data=statistics)
    except Exception as e:
        logger.exception(e)
        return ResponseUtil.error(msg=str(e))


@dashboardRouter.get("/recent-searches")
async def get_recent_searches(
    request: Request,
    limit: int = 10,
    db: AsyncSession = Depends(get_db)
):
    """
    获取最近搜索记录
    """
    try:
        from module_admin.entity.do.search_record_do import SearchRecord
        
        # 查询最近的搜索记录，按时间倒序，去重关键词
        # ✅ 修复：SearchRecord 使用 search_keyword 而不是 keyword
        query = select(
            SearchRecord.search_keyword,
            func.min(SearchRecord.first_found_time).label('first_search_time'),
            func.max(SearchRecord.last_found_time).label('last_search_time'),
            func.count(SearchRecord.id).label('search_count')
        ).group_by(
            SearchRecord.search_keyword
        ).order_by(
            func.max(SearchRecord.last_found_time).desc()
        ).limit(limit)
        
        result = await db.execute(query)
        records = result.all()
        
        recent_searches = [
            {
                'keyword': row.search_keyword or '',
                'first_search_time': row.first_search_time.isoformat() if row.first_search_time else None,
                'last_search_time': row.last_search_time.isoformat() if row.last_search_time else None,
                'search_count': row.search_count
            }
            for row in records
        ]
        
        return ResponseUtil.success(data=recent_searches)
    except Exception as e:
        logger.debug(f"获取搜索记录失败（可能表不存在）: {e}")
        return ResponseUtil.success(data=[])


@dashboardRouter.get("/client-status")
async def get_client_status_distribution(
    request: Request,
    db: AsyncSession = Depends(get_db)
):
    """
    获取客户端状态分布（用于图表展示）
    """
    try:
        # 按登录状态统计
        login_status_query = select(
            AgentClient.login_status,
            func.count(AgentClient.id).label('count')
        ).group_by(AgentClient.login_status)
        
        login_status_result = await db.execute(login_status_query)
        login_status_data = [
            {'status': row.login_status or 'unknown', 'count': row.count}
            for row in login_status_result.all()
        ]
        
        # 按浏览器状态统计
        browser_status_query = select(
            AgentClient.browser_status,
            func.count(AgentClient.id).label('count')
        ).group_by(AgentClient.browser_status)
        
        browser_status_result = await db.execute(browser_status_query)
        browser_status_data = [
            {'status': row.browser_status or 'stopped', 'count': row.count}
            for row in browser_status_result.all()
        ]
        
        return ResponseUtil.success(data={
            'login_status': login_status_data,
            'browser_status': browser_status_data
        })
    except Exception as e:
        logger.exception(e)
        return ResponseUtil.error(msg=str(e))


@dashboardRouter.get("/activity-timeline")
async def get_activity_timeline(
    request: Request,
    limit: int = 20,
    db: AsyncSession = Depends(get_db)
):
    """
    获取最近活动时间线（搜索、下单等）
    """
    try:
        activities = []
        
        # 获取最近搜索活动
        try:
            from module_admin.entity.do.search_record_do import SearchRecord
            
            search_query = select(SearchRecord).order_by(
                SearchRecord.first_found_time.desc()
            ).limit(limit // 2)
            
            search_result = await db.execute(search_query)
            search_records = search_result.scalars().all()
            
            for record in search_records:
                # ✅ 修复：安全访问字段，使用getattr避免字段不存在
                keyword = getattr(record, 'search_keyword', None) or "未知关键词"
                title = getattr(record, 'title', None) or getattr(record, 'item_id', None) or "未知商品"
                found_time = getattr(record, 'first_found_time', None) or getattr(record, 'create_time', None)
                
                activities.append({
                    'type': 'search',
                    'title': f'搜索: {keyword}',
                    'description': f'商品: {title}',
                    'time': found_time.isoformat() if found_time else None,
                    'icon': 'Search',
                    'color': '#1890ff'
                })
        except Exception as e:
            logger.debug(f"获取搜索活动失败: {e}")
        
        # 获取最近下单活动
        try:
            from module_admin.entity.do.order_record_do import OrderRecord
            from sqlalchemy import text
            
            # ✅ 修复：使用正确的字段名（title, price, user_name）
            # 注意：OrderRecord使用标准字段 title, price, user_name（不是 item_title, item_price, client_name）
            try:
                # 尝试查询完整模型（新表结构）
                order_query = select(
                    OrderRecord.id,
                    OrderRecord.item_id,
                    OrderRecord.title,  # ✅ 使用标准字段 title
                    OrderRecord.price,  # ✅ 使用标准字段 price
                    OrderRecord.client_id,
                    OrderRecord.user_name,  # ✅ 使用标准字段 user_name（不是 client_name）
                    OrderRecord.create_time
                ).order_by(
                    OrderRecord.create_time.desc()
                ).limit(limit // 2)
            
                order_result = await db.execute(order_query)
                order_rows = order_result.all()
                
                for row in order_rows:
                    # ✅ 使用 getattr 安全访问字段（兼容可能不存在的情况）
                    title = getattr(row, 'title', None) or getattr(row, 'item_id', None) or '未知商品'
                    price = getattr(row, 'price', None) or 0
                    client_name = getattr(row, 'user_name', None) or getattr(row, 'client_id', None) or '未知'
                    
                    activities.append({
                        'type': 'order',
                        'title': f'下单: {title}',
                        'description': f'价格: ¥{price} | 客户端: {client_name}',
                        'time': row.create_time.isoformat() if row.create_time else None,
                        'icon': 'ShoppingCart',
                        'color': '#52c41a'
                    })
            except Exception as inner_e:
                # 如果查询失败，尝试使用原始SQL查询（使用标准字段名）
                logger.debug(f"使用ORM查询失败，尝试原始SQL: {inner_e}")
                sql = text("""
                    SELECT id, item_id, title, price, client_id, user_name, create_time
                    FROM order_record
                    WHERE del_flag = '0'
                    ORDER BY create_time DESC
                    LIMIT :limit
                """)
                result = await db.execute(sql, {'limit': limit // 2})
                rows = result.fetchall()
                
                for row in rows:
                    # ✅ 使用 getattr 安全访问字段
                    title = getattr(row, 'title', None) or getattr(row, 'item_id', None) or '未知商品'
                    price = getattr(row, 'price', None) or 0
                    client_name = getattr(row, 'user_name', None) or getattr(row, 'client_id', None) or '未知'
                    
                activities.append({
                    'type': 'order',
                        'title': f'下单: {title}',
                        'description': f'价格: ¥{price} | 客户端: {client_name}',
                        'time': row.create_time.isoformat() if row.create_time else None,
                    'icon': 'ShoppingCart',
                    'color': '#52c41a'
                })
        except Exception as e:
            logger.debug(f"获取订单活动失败: {e}")
        
        # 按时间排序
        activities.sort(key=lambda x: x['time'] or '', reverse=True)
        
        return ResponseUtil.success(data=activities[:limit])
    except Exception as e:
        logger.exception(e)
        return ResponseUtil.error(msg=str(e))


@dashboardRouter.delete("/clean-offline-clients")
async def clean_offline_clients(
    request: Request,
    db: AsyncSession = Depends(get_db)
):
    """
    清理离线客户端记录
    """
    try:
        # 删除所有online_status != 'online'的客户端
        delete_query = select(AgentClient).where(
            AgentClient.online_status != 'online'
        )
        
        result = await db.execute(delete_query)
        offline_clients = result.scalars().all()
        
        count = 0
        for client in offline_clients:
            await db.delete(client)
            count += 1
        
        await db.commit()
        
        logger.info(f"清理了 {count} 个离线客户端记录")
        return ResponseUtil.success(msg=f'成功清理 {count} 个离线客户端', data={'count': count})
    except Exception as e:
        await db.rollback()
        logger.exception(e)
        return ResponseUtil.error(msg=str(e))

